@ECHO OFF
:MainMenu
CLS
COLOR 0A
ECHO.
ECHO **************************************************
ECHO ************ DATABASE CREATION SCRIPT ************
ECHO **************************************************
ECHO.
ECHO SELECT ONE:
ECHO **************************************************
ECHO ==================================================
ECHO 1) RUN SCRIPT ON - LOCAL DB USING WINDOWS AUTH
ECHO 2) RUN SCRIPT ON - LOCAL DB USING SQL-SERVER AUTH
ECHO 3) RUN SCRIPT ON - PRODUCTION DB SERVER
ECHO 4) RUN SCRIPT ON - CLOUD DEV DB SERVER
ECHO ==================================================
ECHO Type Q to Quit.
ECHO ==================================================
SET /p OPTIONMainMenu=TYPE YOUR OPTIONTION:

IF "%OPTIONMainMenu%"=="1" GOTO LocalWinAuth
IF "%OPTIONMainMenu%"=="2" GOTO LocalSqlAuth
IF "%OPTIONMainMenu%"=="3" GOTO ProdServer
IF "%OPTIONMainMenu%"=="4" GOTO DevServer
IF "%OPTIONMainMenu%"=="Q" GOTO END
IF "%OPTIONMainMenu%"=="q" GOTO END
ECHO Please Pick an option:
GOTO MainMenu
::=========================================================

:ActivityMenu
CLS
COLOR 0E
ECHO.
ECHO %L1%
ECHO %L2%
ECHO %L3%
ECHO %L4%
ECHO.
ECHO SELECT ONE:
ECHO ======================================================== 
ECHO 1) RUN SCRIPT TO CREATE TABLE, STORED PROC, DEFAULT DATA
ECHO 2) RUN SCRIPT TO CREATE ALL STORED PROCEDURES ONLY
ECHO ========================================================
ECHO Type Q to Quit.
ECHO ========================================================
SET /p OPTIONActivityMenu=TYPE YOUR OPTIONTION:
IF "%OPTIONActivityMenu%"=="1" GOTO RunAllScripts
IF "%OPTIONActivityMenu%"=="2" GOTO RunStoredProcScripts
IF "%OPTIONActivityMenu%"=="Q" GOTO END
IF "%OPTIONActivityMenu%"=="q" GOTO END
ECHO Please Pick an option:
GOTO :ActivityMenu


:LocalWinAuth
SET L1=********************************************************
SET L2=******************** LOCAL DATABASE ********************
SET L3=****************** USING WINDOWS AUTH ******************
SET L4=********************************************************
ECHO.
SET /p SQLSERVER= Give the SQL Server instance name?
SET DATABASENAME=FrameworkIvyDB
SET USERNAME=0
SET PWD=0
SET TABLECREATIONSCRIPT=DbAllTableCreationScriptForLocalDB.SQLCMD
GOTO ActivityMenu
::===========================================================

:LocalSqlAuth
SET L1=********************************************************
SET L2=******************** LOCAL DATABASE ********************
SET L3=******************** USING SQL AUTH ********************
SET L4=********************************************************
ECHO.
SET /p SQLSERVER= Give the SQL Server instance name?
SET /p DATABASENAME= Give the DATABASE name?
SET /p USERNAME= Give the user name?
SET /p PWD= Give the password?
SET TABLECREATIONSCRIPT=DbAllTableCreationScriptForLocalDB.SQLCMD
GOTO ActivityMenu
::===========================================================

:ProdServer
SET L1=********************************************************
SET L2=***************** PRODUCTION  DATABASE *****************
SET L3=********************************************************
SET L4= 
SET SQLSERVER=frameworkivy.db.9734415.hostedresource.com
SET DATABASENAME=frameworkivy
SET /p USERNAME= Give the user name?
SET /p PWD= Give the password?
SET TABLECREATIONSCRIPT=DbAllTableCreationScriptForProdDB.SQLCMD
GOTO ActivityMenu
::===========================================================

:DevServer
SET L1=********************************************************
SET L2=************** CLOUD DEVELOPMENT DATABASE **************
SET L3=********************************************************
SET L4= 
SET /p SQLSERVER= Give the SQL Server instance name?
SET /p DATABASENAME= Give the DATABASE name?
SET /p USERNAME= Give the user name?
SET /p PWD= Give the password?
SET TABLECREATIONSCRIPT=DbAllTableCreationScriptForProdDB.SQLCMD
GOTO ActivityMenu
::===========================================================


:RunAllScripts

IF "%USERNAME%"=="0" (
ECHO.
SQLCMD -S %SQLSERVER% -E -d %DATABASENAME% -i %TABLECREATIONSCRIPT%
PAUSE
) ELSE (
ECHO.
SQLCMD -S %SQLSERVER% -U %USERNAME% -P %PWD% -d %DATABASENAME% -i %TABLECREATIONSCRIPT%
PAUSE
)
::===========================================================


:RunStoredProcScripts
ECHO Plese Wait ... [Finding Stored Procedures from all folders.]
@ECHO OFF
DIR ..\..\..\SqlServer*.* /S/B > AllStoredProcedureList.txt 
::PAUSE

IF EXIST .\StoredProcedure ERASE .\StoredProcedure\*.* /Q 
IF NOT EXIST .\StoredProcedure MKDIR .\StoredProcedure 
ECHO Plese Wait for couple of seconds ... [Creating local folder.]
::PAUSE

FOR /F %%L IN (AllStoredProcedureList.txt) DO  (COPY %%L .\StoredProcedure /Y > NUL)
ECHO Plese Wait for couple of seconds ... [Copying all sql files.]
::PAUSE

DIR .\StoredProcedure\*.* /S/B > AllStoredProcedureList.txt
rem DIR .\StoredProcedure\*.* /B 2>NUL
DIR .\StoredProcedure\*.* | Find "File(s)" 2>NUL
COPY /Y NUL TempDbAllStoredProcCreationScripts.SQLCMD > NUL
ECHO Plese Wait for couple of seconds ... [Collecting info for dynamic script file.]
::PAUSE

ECHO -- ============================================= >> TempDbAllStoredProcCreationScripts.SQLCMD
ECHO -- Script Template  >> TempDbAllStoredProcCreationScripts.SQLCMD
ECHO -- ============================================= >> TempDbAllStoredProcCreationScripts.SQLCMD
ECHO PRINT '** STARTING STORED PROCEDURE CREATION ...' >> TempDbAllStoredProcCreationScripts.SQLCMD
ECHO GO >> TempDbAllStoredProcCreationScripts.SQLCMD
ECHO :On ERROR EXIT >> TempDbAllStoredProcCreationScripts.SQLCMD
FOR /F %%L in (AllStoredProcedureList.txt) DO ECHO :r %%L >> TempDbAllStoredProcCreationScripts.SQLCMD
ECHO Plese Wait for couple of seconds ... [Creating dynamic script file.]
::PAUSE

ECHO PRINT '** STORED PROCEDURE CREATED SUCCESSFULLY' >> TempDbAllStoredProcCreationScripts.SQLCMD
ECHO GO >> TempDbAllStoredProcCreationScripts.SQLCMD
ECHO Plese Wait for couple of seconds ... [Dynamic script file created...]
::PAUSE

IF "%USERNAME%"=="0" (
ECHO.
SQLCMD -S %SQLSERVER% -E -d %DATABASENAME% -i TempDbAllStoredProcCreationScripts.SQLCMD
PAUSE
) ELSE (
ECHO.
SQLCMD -S %SQLSERVER% -U %USERNAME% -P %PWD% -d %DATABASENAME% -i TempDbAllStoredProcCreationScripts.SQLCMD
PAUSE
)
IF "%OPTIONActivityMenu%"=="2" GOTO END
::===========================================================

:RunDefaultDataCreationScript
IF "%USERNAME%"=="0" (
ECHO.
CD .\DefaultDataScripts > NUL
SQLCMD -S %SQLSERVER% -E -d %DATABASENAME% -i DbAllDefaultDataCreationScripts.SQLCMD
PAUSE
) ELSE (
ECHO.
CD .\DefaultDataScripts > NUL
SQLCMD -S %SQLSERVER% -U %USERNAME% -P %PWD% -d %DATABASENAME% -i DbAllDefaultDataCreationScripts.SQLCMD
PAUSE
)
ECHO.
CD ..\ > NUL
::===========================================================


:END
ECHO Thank you ...
PAUSE
@ECHO ON
@exit

::===========================================================
